﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

/// <summary>
/// Summary description for _Invoice
/// </summary>
public class _Invoice
{
    public _Invoice()
    {
        // 
        // TODO: Add constructor logic here
        //
    }
    public int id { get; set; }
    public string document_number { get; set; }
    public DateTime document_date { get; set; }
    public DateTime document_payment_date { get; set; }
    public int id_issued_by_user { get; set; }
    public string issued_by_user { get; set; }
    public int id_company_issued_by { get; set; }
    public string company_issued_by { get; set; }
    public string company_issued_by_regnr { get; set; }
    public string company_issued_by_vat { get; set; }
    public int id_client_issued_to { get; set; }
    public string client_issued_to { get; set; }
    public string client_issued_to_regnr { get; set; }
    public string client_issued_to_vat { get; set; }
    public DateTime date_ceated { get; set; }
    public int id_document_type { get; set; }
    public string document_type { get; set; }
    public int id_payment_type { get; set; }
    public string payment_type { get; set; }
    public string place_of_delivery { get; set; }
    public int id_address_company_by { get; set; }
    public string address_company_by { get; set; }
    public int id_address_client_to { get; set; }
    public string address_client_to { get; set; }
    public int id_bank_company_by { get; set; }
    public string bank_company_by { get; set; }
    public string bank_company_by_swift { get; set; }
    public int id_bank_client_to { get; set; }
    public string bank_client_to { get; set; }
    public string bank_client_to_swift { get; set; }
    public int id_account_company_by { get; set; }
    public string account_company_by { get; set; }
    public int id_account_client_to { get; set; }
    public string account_client_to { get; set; }
    public string status { get; set; }

    static SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);

    private static _Invoice FillInvoice(SqlDataReader dr)
    {
        _Invoice invoice = new _Invoice();

        invoice.id = (int)dr[0];
        invoice.document_number = dr[1].ToString();
        invoice.document_date = (DateTime)dr[2];
        invoice.document_payment_date = (DateTime)dr[3];

        invoice.id_issued_by_user = (int)dr[4];
        invoice.issued_by_user = _User.SelectUser(dr[4].ToString())._Name + " " + _User.SelectUser(dr[4].ToString())._SecondName;

        invoice.id_company_issued_by = (int)dr[5];
        invoice.company_issued_by = _Company.SelectCompany(dr[5].ToString(),"")._company_name;
        invoice.id_client_issued_to = (int)dr[6];
        invoice.client_issued_to = _Company.SelectCompany(dr[6].ToString(),"")._company_name;

        //invoice.date_ceated = (DateTime)dr[7];

        invoice.document_type = _Document_type.SelectDocType(dr[8].ToString())._document_type;
        invoice.payment_type = _Payment_type.SelectPayType(dr[9].ToString())._type_name;
        invoice.place_of_delivery = dr[10].ToString();

        invoice.id_address_company_by = (int)dr[11];
        invoice.id_address_client_to = (int)dr[12];
        invoice.id_bank_company_by = (int)dr[13];
        invoice.id_bank_client_to = (int)dr[14];
        invoice.id_account_company_by = (int)dr[15];
        invoice.id_account_client_to = (int)dr[16];
        invoice.status = dr[17].ToString();
        return invoice;
    }

    //Atlasa ielogotā lietotaja, lietotāju dokumentus Default.aspx
    public static List<_Invoice> SelectInvoices(string main_user)
    {
        List<_Invoice> Invoice = new List<_Invoice>();
        List<int> users_id = new List<int>();
        List<int> document_id = new List<int>();
        List<int> documents = new List<int>();

        SqlCommand SelCmd = new SqlCommand("SELECT id FROM USERS WHERE main_user=@main_user", conn);
        SelCmd.Parameters.Add(new SqlParameter("@main_user", System.Data.SqlDbType.Int)).Value = main_user;
        conn.Close();
        conn.Open();
        SqlDataReader dr2 = SelCmd.ExecuteReader();
        while (dr2.Read())
        {
            users_id.Add(Convert.ToInt32(dr2[0].ToString()));
        }
        dr2.Close();
        conn.Close();

        // Jāpārčeko te ir kaut kas par daudz...
        foreach (int user in users_id)
        {
            SqlCommand selCmd = new SqlCommand("SELECT id FROM DOCUMENT WHERE id_issued_by_user=@main_user", conn);
            selCmd.Parameters.Add(new SqlParameter("@main_user", System.Data.SqlDbType.Int)).Value = user;
            conn.Open();
            SqlDataReader dr = selCmd.ExecuteReader();
            while (dr.Read())
            {
                document_id.Add(Convert.ToInt32(dr["id"].ToString()));
            }
            dr.Close();
            documents.AddRange(document_id);
            document_id.Clear();
            conn.Close();
        }

        foreach (int document in documents)
        {
            SqlCommand selCmd2 = new SqlCommand("SELECT * FROM DOCUMENT WHERE id=@id", conn);
            selCmd2.Parameters.Add(new SqlParameter("@id", System.Data.SqlDbType.Int)).Value = document;
            conn.Open();
            SqlDataReader dr = selCmd2.ExecuteReader();
            while (dr.Read())
            {
                Invoice.Add(FillInvoice(dr));
            }
            dr.Close();
            conn.Close();
        }
        return Invoice;
    }

    //Atlasa ielogotā lietotāja dokumentus Default.aspx
    public static List<_Invoice> Select_My_Invoices(string document_number_search, string status, string main_user)
    {
        List<_Invoice> Invoice = new List<_Invoice>();
        conn.Open();
        SqlCommand selCmd = new SqlCommand("SELECT * FROM DOCUMENT WHERE id_issued_by_user=@main_user AND (LOWER(ISNULL(document_number,'')) LIKE '%'+LOWER(@document_number)+'%') AND ISNULL(statuss, '') <> '' AND (ISNULL(@statuss, '') = '' OR ISNULL(@statuss, '') = statuss)", conn);
        selCmd.Parameters.Add(new SqlParameter("@main_user", System.Data.SqlDbType.Int)).Value = main_user;
        selCmd.Parameters.Add(new SqlParameter("@document_number", System.Data.SqlDbType.NVarChar)).Value = document_number_search;
        selCmd.Parameters.Add(new SqlParameter("@statuss", System.Data.SqlDbType.NVarChar)).Value = status; 
        SqlDataReader dr = selCmd.ExecuteReader();
        while (dr.Read())
        {
            Invoice.Add(FillInvoice(dr));
        }
        dr.Close();
        conn.Close();
        return Invoice;
    }

    //Atlasa dokumenta datus priekš ViewInvoice.aspx
    public static _Invoice ViewInvoice(string document_id)
    {
        _Invoice Invoice = new _Invoice();
        SqlCommand selCmd = new SqlCommand("SELECT * FROM DOCUMENT WHERE id=@id", conn);
        selCmd.Parameters.Add(new SqlParameter("@id", System.Data.SqlDbType.Int)).Value = document_id;
        conn.Open();
        SqlDataReader dr = selCmd.ExecuteReader();
        while (dr.Read())
        {
            Invoice.document_number = dr[1].ToString();
            Invoice.document_date = (DateTime) dr[2];
            Invoice.document_payment_date = (DateTime) dr[3];
            Invoice.issued_by_user = _User.SelectUser(dr[4].ToString())._Name + " " +_User.SelectUser(dr[4].ToString())._SecondName;
            Invoice.id_company_issued_by = _Company.SelectCompany(dr[5].ToString(), "")._id;
            Invoice.company_issued_by = _Company.SelectCompany(dr[5].ToString(), "")._company_name;
            Invoice.company_issued_by_regnr = _Company.SelectCompany(dr[5].ToString(),"")._company_reg_nr;
            Invoice.company_issued_by_vat = _Company.SelectCompany(dr[5].ToString(),"")._company_vat;
            Invoice.id_client_issued_to = _Company.SelectCompany(dr[6].ToString(),"")._id;
            Invoice.client_issued_to = _Company.SelectCompany(dr[6].ToString(),"")._company_name;
            Invoice.client_issued_to_regnr = _Company.SelectCompany(dr[6].ToString(),"")._company_reg_nr;
            Invoice.client_issued_to_vat = _Company.SelectCompany(dr[6].ToString(),"")._company_vat;
            //Invoice.date_ceated = (DateTime) dr[7];
            Invoice.document_type = _Document_type.SelectDocType(dr[8].ToString())._document_type;
            Invoice.payment_type = _Payment_type.SelectPayType(dr[9].ToString())._type_name;
            Invoice.place_of_delivery = dr[10].ToString();
            Invoice.id_address_company_by = _Adress.Select(dr[11].ToString())._id_address;
            Invoice.address_company_by = _Adress.Select(dr[11].ToString())._address;
            Invoice.id_address_client_to = _Adress.Select(dr[12].ToString())._id_address;
            Invoice.address_client_to = _Adress.Select(dr[12].ToString())._address;
            Invoice.id_bank_company_by = _Bank.Select(dr[13].ToString())._id;
            Invoice.bank_company_by = _Bank.Select(dr[13].ToString())._bank_name;
            Invoice.bank_company_by_swift = _Bank.Select(dr[13].ToString())._bank_swift;
            Invoice.id_bank_client_to = _Bank.Select(dr[14].ToString())._id;
            Invoice.bank_client_to = _Bank.Select(dr[14].ToString())._bank_name;
            Invoice.bank_client_to_swift = _Bank.Select(dr[14].ToString())._bank_swift;
            Invoice.id_account_company_by = _Account.SelectByDocumentAccountID(dr[15].ToString())._id_account_number;
            Invoice.account_company_by = _Account.SelectByDocumentAccountID(dr[15].ToString())._account_number;
            Invoice.id_account_client_to = _Account.SelectByDocumentAccountID(dr[16].ToString())._id_account_number;
            Invoice.account_client_to = _Account.SelectByDocumentAccountID(dr[16].ToString())._account_number;
        }
        dr.Close();
        conn.Close();
        return Invoice;
    }

    //Vērtību atlasīšana priekš EditDocument.aspx
    public static _Invoice EditDocument(string id)
    {
        _Invoice Invoice = new _Invoice();
        SqlCommand commandz = new SqlCommand("SELECT * FROM DOCUMENT WHERE id = @id", conn);
        commandz.Parameters.Add(new SqlParameter("@id", System.Data.SqlDbType.Int)).Value = id;
        conn.Open();
        SqlDataReader dr = commandz.ExecuteReader();
        while (dr.Read())
        {
            Invoice.id = (int) dr[0];
            Invoice.document_number = dr[1].ToString();
            Invoice.document_date = (DateTime) dr[2];
            Invoice.document_payment_date = (DateTime) dr[3];
            Invoice.id_issued_by_user = (int) dr[4];
            Invoice.id_company_issued_by = (int) dr[5];
            Invoice.id_client_issued_to = (int) dr[6];
            //Invoice.date_ceated = (DateTime) dr[7];
            Invoice.id_document_type = (int) dr[8];
            Invoice.id_payment_type = (int) dr[9];
            Invoice.place_of_delivery = dr[10].ToString();
            Invoice.id_address_company_by = (int) dr[11];
            Invoice.id_address_client_to = (int) dr[12];
            Invoice.id_bank_company_by = (int) dr[13];
            Invoice.id_bank_client_to = (int) dr[14];
            Invoice.id_account_company_by = (int) dr[15];
            Invoice.id_account_client_to = (int) dr[16];
        }
        dr.Close();
        conn.Close();
        return Invoice;
    }

    //Dokumenta labošana EditDocunemt.aspx
    public static _Invoice Update(string id, string doc_num, string doc_date, string doc_pay_date, string id_isu_user, string id_company_by, string id_client_to, string id_toc_type, string id_pay_type, string delivery_place, string id_addres_company_by, string id_address_client_to, string id_bank_company_by, string id_bank_client_to, string id_account_company_by, string id_account_client_to, string statuss)
    {
        _Invoice invoice = new _Invoice();
        SqlCommand UpCmd = new SqlCommand("UPDATE DOCUMENT SET document_number=@document_number, document_date=@document_date, document_payment_date=@document_payment_date, " +
                "id_issued_by_user=@id_issued_by_user, id_company_issued_by=@id_company_issued_by, id_client_issued_to=@id_client_issued_to, " +
                "id_document_type=@id_document_type, id_payment_type=@id_payment_type, place_of_delivery=@place_of_delivery, id_address_company_by=@id_address_company_by, " +
                "id_address_client_to=@id_address_client_to, id_bank_company_by=@id_bank_company_by, id_bank_client_to=@id_bank_client_to, id_account_company_by=@id_account_company_by, " +
                "id_account_client_to=@id_account_client_to, statuss=@statuss WHERE ID=@ID", conn);
        UpCmd.Parameters.Add(new SqlParameter("@ID", System.Data.SqlDbType.Int)).Value = id;
        UpCmd.Parameters.Add(new SqlParameter("@document_number", System.Data.SqlDbType.NVarChar)).Value = doc_num;
        UpCmd.Parameters.Add(new SqlParameter("@document_date", System.Data.SqlDbType.DateTime)).Value = doc_date;
        UpCmd.Parameters.Add(new SqlParameter("@document_payment_date", System.Data.SqlDbType.DateTime)).Value =doc_pay_date;
        UpCmd.Parameters.Add(new SqlParameter("@id_issued_by_user", System.Data.SqlDbType.Int)).Value = id_isu_user;
        UpCmd.Parameters.Add(new SqlParameter("@id_company_issued_by", System.Data.SqlDbType.Int)).Value = id_company_by;
        UpCmd.Parameters.Add(new SqlParameter("@id_client_issued_to", System.Data.SqlDbType.Int)).Value = id_client_to;
        //UpCmd.Parameters.Add(new SqlParameter("@document_path", System.Data.SqlDbType.NVarChar)).Value = doc_path;
        UpCmd.Parameters.Add(new SqlParameter("@id_document_type", System.Data.SqlDbType.Int)).Value = id_toc_type;
        UpCmd.Parameters.Add(new SqlParameter("@id_payment_type", System.Data.SqlDbType.Int)).Value = id_pay_type;
        UpCmd.Parameters.Add(new SqlParameter("@place_of_delivery", System.Data.SqlDbType.NVarChar)).Value =delivery_place;
        UpCmd.Parameters.Add(new SqlParameter("@id_address_company_by", System.Data.SqlDbType.Int)).Value =id_addres_company_by;
        UpCmd.Parameters.Add(new SqlParameter("@id_address_client_to", System.Data.SqlDbType.Int)).Value =id_address_client_to;
        UpCmd.Parameters.Add(new SqlParameter("@id_bank_company_by", System.Data.SqlDbType.Int)).Value =id_bank_company_by;
        UpCmd.Parameters.Add(new SqlParameter("@id_bank_client_to", System.Data.SqlDbType.Int)).Value =id_bank_client_to;
        UpCmd.Parameters.Add(new SqlParameter("@id_account_company_by", System.Data.SqlDbType.Int)).Value =id_account_company_by;
        UpCmd.Parameters.Add(new SqlParameter("@id_account_client_to", System.Data.SqlDbType.Int)).Value =id_account_client_to;
        UpCmd.Parameters.Add(new SqlParameter("@statuss", System.Data.SqlDbType.VarChar)).Value = statuss;
        conn.Open();
        UpCmd.ExecuteNonQuery();
        conn.Close();
        return invoice;
    }

    //Dokumenta pievienošana AddDocument.aspx
    public static _Invoice Insert(string doc_num, string doc_date, string doc_pay_date, string id_isu_user, string id_company_by, string id_company_to, string id_toc_type, string id_pay_type, string delivery_place, string id_addres_company_by, string id_address_client_to, string id_bank_company_by, string id_bank_client_to, string id_account_company_by, string id_account_client_to, string statuss)
    {
        _Invoice invoice = new _Invoice();
        SqlCommand AddCmd = new SqlCommand("INSERT INTO DOCUMENT (document_number, document_date, document_payment_date, id_issued_by_user, id_company_issued_by, id_client_issued_to, id_document_type, " +
                                           "id_payment_type, place_of_delivery, id_address_company_by, id_address_client_to, id_bank_company_by, id_bank_client_to, id_account_company_by, id_account_client_to, statuss) VALUES " +
                                           "(@document_number, @document_date, @document_payment_date, @id_issued_by_user, @id_company_issued_by, @id_client_issued_to, @id_document_type, @id_payment_type," +
                                           "@place_of_delivery, @id_address_company_by, @id_address_client_to, @id_bank_company_by, @id_bank_client_to, @id_account_company_by, @id_account_client_to, @statuss)", conn);
        AddCmd.Parameters.Add(new SqlParameter("@document_number", System.Data.SqlDbType.NVarChar)).Value = doc_num;
        AddCmd.Parameters.Add(new SqlParameter("@document_date", System.Data.SqlDbType.DateTime)).Value = doc_date;
        AddCmd.Parameters.Add(new SqlParameter("@document_payment_date", System.Data.SqlDbType.DateTime)).Value = doc_pay_date;
        AddCmd.Parameters.Add(new SqlParameter("@id_issued_by_user", System.Data.SqlDbType.Int)).Value = id_isu_user;
        AddCmd.Parameters.Add(new SqlParameter("@id_company_issued_by", System.Data.SqlDbType.Int)).Value = id_company_by;
        AddCmd.Parameters.Add(new SqlParameter("@id_client_issued_to", System.Data.SqlDbType.Int)).Value = id_company_to;
        //AddCmd.Parameters.Add(new SqlParameter("@document_path", System.Data.SqlDbType.NVarChar)).Value = doc_path;
        AddCmd.Parameters.Add(new SqlParameter("@id_document_type", System.Data.SqlDbType.Int)).Value = id_toc_type;
        AddCmd.Parameters.Add(new SqlParameter("@id_payment_type", System.Data.SqlDbType.Int)).Value = id_pay_type;
        AddCmd.Parameters.Add(new SqlParameter("@place_of_delivery", System.Data.SqlDbType.NVarChar)).Value = delivery_place;
        AddCmd.Parameters.Add(new SqlParameter("@id_address_company_by", System.Data.SqlDbType.Int)).Value = id_addres_company_by;
        AddCmd.Parameters.Add(new SqlParameter("@id_address_client_to", System.Data.SqlDbType.Int)).Value = id_address_client_to;
        AddCmd.Parameters.Add(new SqlParameter("@id_bank_company_by", System.Data.SqlDbType.Int)).Value = id_bank_company_by;
        AddCmd.Parameters.Add(new SqlParameter("@id_bank_client_to", System.Data.SqlDbType.Int)).Value = id_bank_client_to;
        AddCmd.Parameters.Add(new SqlParameter("@id_account_company_by", System.Data.SqlDbType.Int)).Value = id_account_company_by;
        AddCmd.Parameters.Add(new SqlParameter("@id_account_client_to", System.Data.SqlDbType.Int)).Value = id_account_client_to;
        AddCmd.Parameters.Add(new SqlParameter("@statuss", System.Data.SqlDbType.VarChar)).Value = statuss;
        conn.Open();
        AddCmd.ExecuteNonQuery();
        conn.Close();
        return invoice;
    }

    public static _Invoice DocNumber(string DocType, string DocNumber, List<string> doc_num)
    {
        _Invoice invoice = new _Invoice();
        SqlCommand sCmd_doc_num = new SqlCommand("SELECT document_number FROM document WHERE document_number LIKE '" + DocNumber.ToUpper() + "%' AND id_document_type=@document_type_id;", conn);
        sCmd_doc_num.Parameters.Add(new SqlParameter("@document_type_id", SqlDbType.Int)).Value = DocType;
        conn.Open();
        SqlDataReader dr = sCmd_doc_num.ExecuteReader();
        while (dr.Read())
        {
            doc_num.Add(dr["document_number"].ToString());
        }
        dr.Close();
        conn.Close();
        return invoice;
    }

    //Dokumenta dzēšana MyInvoice.aspx
    //public static _Invoice Delete(string id_document)
    //{
    //    _Invoice invoice = new _Invoice();
    //    SqlCommand DelCmd = new SqlCommand("DELETE FROM DOCUMENT WHERE (ID = @id_document)", conn);
    //    DelCmd.Parameters.Add(new SqlParameter("@id_document", System.Data.SqlDbType.Int)).Value = id_document;
    //    conn.Open();
    //    DelCmd.ExecuteNonQuery();
    //    conn.Close();
    //    return invoice;
    //}
}